------------------------
-----$$$$$$$$$$$$$$$----
--in eas database
--select org from eas
/*
select FID,FNumber,fname_l2,fdescription_L2,fparentid,flongnumber 
from T_org_admin 
order by flongnumber

select * from firstoa_main.dbo.systemgroups
*/

--delete dept history data
delete  from firstoa_main.dbo.systemgroups
where groupid >= 3


--insert dept data
insert into firstoa_main.dbo.systemgroups(groupname,description,isdept,easgroupID,easNumber,easParentID,easLongNumber,leaderorder,easDisplayName,isManageDept)
select fname_l2,fdescription_L2,'1',FID,FNumber,fparentid,flongnumber,99,FDisplayName_L2,FIsCU
from T_Org_BaseUnit 
order by flongnumber

/*
select upper(fid),count(*) cn from T_Org_BaseUnit
group by upper(fid)
order by cn desc
*/

------------------------
------------------------
--#######notice########
--in firstoa_main
--update org's parentID

update systemgroups 
set parentID = isnull((select max(groupid) from systemgroups b where b.easgroupID = systemgroups.easParentID ),0)
where isdept = 1 


--check the easgroupid is distinct
/*
select easgroupid,count(*) cn from systemgroups
group by easgroupid
order by cn desc
*/


------------------------
------------------------
------------------------
--#######notice########
--in eas dababase
-----------------------
/*

select * from T_PM_User
select * from firstoa_main.dbo.systemgroups
select * from firstoa_main.dbo.systemusers
select * from firstoa_main.dbo.systemgroupmembership
select * from firstoa_main.dbo.systemusage
select * from firstoa_main.dbo.ResourceBooking
*/
---delete history data
delete from firstoa_main.dbo.systemusage
where userid >1
delete from firstoa_main.dbo.ResourceBooking
where userid >1
delete from firstoa_main.dbo.systemgroupmembership
where userid >1
delete from firstoa_main.dbo.systemusers
where userid >1

-------------------------
-------in eas
--insert  users
insert into firstoa_main.dbo.systemusers(username,fullname,easUserID,Address1,status,StyleID)
select usr.fnumber,usr.fname_l2,usr.fid,isnull(bs.orgunitid,usr.fdeforgunitid),1,1 from t_pm_user usr
left outer join 
(select pmuser.fid,pmuser.ftype,pmuser.FNumber,pmuser.FName_L2,person.FGender,person.Fbirthday,person.FEmail,
person.FAddress_L2,person.FHomePhone,person.FOfficePhone,person.FCell,e.fid as orgunitid,e.fname_l2 as orgname
from t_pm_user pmuser
left outer join t_bd_person person on pmuser.fpersonid = person.fid
left outer join t_org_positionmember c on person.fid = c.fpersonid
left outer join t_org_position d on d.fid = c.fpositionid
left outer join t_org_baseunit e on e.fid = d.fadminorgunitid
 where  c.fisPrimary = 1 ) bs on usr.fid = bs.fid
where usr.fisdelete = 0
and usr.fnumber != 'administrator'

--------------------------------
--将管理员放在集团根目录下
insert into firstoa_main.dbo.systemgroupmembership(userid,groupid)
select 1,groupid from firstoa_main.dbo.systemgroups
where parentID = 0
and isdept = 1 


insert into firstoa_main.dbo.systemgroupmembership(groupid,userid)
select 2,userid
from firstoa_main.dbo.systemusers 
where  userid >1

insert into firstoa_main.dbo.systemgroupmembership(groupid,userid)
select groups.groupid,users.userid
 from  firstoa_main.dbo.systemusers users, firstoa_main.dbo.systemgroups groups
where users.address1 = easgroupid
